<?php
    include_once '../Utilities/connect.php';
    $conn=  getConnect();
    mysql_set_charset('utf8',$conn);
    function getRecordReport($display)
    {
    $conn=  getConnect();
    $sql="select count(OrderID) from orders";
    $res=  mysql_query($sql, $conn) or die('Could not query excuted');
    $rows=  mysql_fetch_array($res, MYSQL_NUM);
    $record=$rows[0];
    if($record>$display)
        $page=  ceil ($record/$display);
    else
        $page=1;
    return $page;
    mysql_close($conn);
    }
    function getDetail($OrderID)
    {
     $conn=  getConnect();
     $sql= "select OrderID,ord.CustomerID,CustomerName,CreatedDate,ShipDate,toAddress,toName,DescriptionOther,
         ModifiedDate,Status,ord.PaymentID,Method,Total 
         from orders ord,customer cs,Paymentmethod pay
         where  ord.CustomerID=cs.CustomerID and ord.PaymentID=pay.PaymentID and OrderID='$OrderID'";
		$rl=mysql_query($sql, getConnect());
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
    }
    function getOrders()
    {
     $conn=  getConnect();
     $sql= "select OrderID,CustomerName,Status,Total,CreatedDate,ModifiedDate,ShipDate
         from orders ord,customer cs
         where  ord.CustomerID=cs.CustomerID 
         order by Status";
		$rl=mysql_query($sql, getConnect());
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
    }
    
    function getOrderDetail($OrderID)
{
     $conn=  getConnect();
    $sql= "select DetailID,orde.ProductID,ProductName,Price,Discount,Quantity,Amount 
        from orderdetail orde,product pr 
        where pr.ProductID=orde.ProductID and OrderID='$OrderID'";
		$rl=mysql_query($sql, getConnect());
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
}
function getTopProduct()
{
     $conn=  getConnect();
    $sql= "SELECT sum( Quantity ) AS tong, ord.ProductID, ProductName, Image, Price, CategoryName
        FROM orderdetail ord, product pr, Categories cate
        WHERE ord.ProductID = pr.ProductID
        AND cate.CategoryID = pr.CategoryID
        GROUP BY ProductID
        ORDER BY tong DESC
        LIMIT 0 , 10";
       $rl=mysql_query($sql, getConnect());
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
}
function getTopProductMonth()
{
     $conn=  getConnect();
    $sql= "SELECT  CreatedDate, sum( Quantity ) AS tong, ord.ProductID, ProductName, Image, Price, CategoryName
        FROM orderdetail ord, product pr, Categories cate, orders
        WHERE ord.ProductID = pr.ProductID
        AND cate.CategoryID = pr.CategoryID
        AND orders.OrderID = ord.OrderID
        GROUP BY ProductID, ProductName, Image, Price, CategoryName, CreatedDate
        ORDER BY tong DESC
        LIMIT 0 , 30";
       $rl=mysql_query($sql, getConnect());
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
}
function DeleteOrder($OrderID)
{
     $conn=  getConnect();
    $sql="delete from orders where OrderID='$OrderID'";
    $rs=  mysql_query($sql, $conn);
    return $rs;
    mysql_close($conn);
}
function UpdateOrder($OrderID,$status,$shipdate,$total,$modifiedDate)
{
     $conn=  getConnect();
    $sql="UPDATE orders SET Status='$status',ShipDate='$shipdate',Total='$total',ModifiedDate='$modifiedDate' WHERE OrderID='$OrderID'";
        $result=  mysql_query($sql,$conn);
        if(!$result)
            die('error query');
        return $result;
        mysql_close($conn);
}
function getRevenue($start,$end)
{
    $conn=  getConnect();
    $sql="SELECT sum( total )
            FROM `orders`
            WHERE CreatedDate
            BETWEEN '$start'
            AND '$end'
            AND STATUS =1";
       $rl=mysql_query($sql,$conn);
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
}
function Product_Statistic($start,$end)
{
    $conn=  getConnect();
    $sql="SELECT Sum( Quantity ) , odd.ProductID, ProductName, CreatedDate
            FROM `orderdetail` odd, product pro, orders od
            WHERE CreatedDate
            BETWEEN '$start'
            AND '$end'
            AND odd.ProductID = pro.ProductID
            AND od.OrderID = odd.OrderID
            GROUP BY odd.ProductID, ProductName, CreatedDate";
    $rl=mysql_query($sql,$conn);
		$arr=array();
		while ($row = mysql_fetch_array($rl))
		{
		 	$arr[]=$row;
		}
		return $arr;
    mysql_close($conn);
}
?>
